-- 设置合并小文件
set hive.merge.mapfiles = true;
set hive.merge.mapredfiles = true;
set hive.merge.size.per.task = 256000000;
set hive.merge.smallfiles.avgsize=16000000;

-- 设置动态分区
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=10000;
set hive.exec.max.created.files=10000;

-- 设置压缩
-- 中间结果设置压缩
set hive.exec.compress.intermediate=true;
set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
-- map端输出压缩
set mapreduce.map.output.compress=true;
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
-- reduce端输出压缩
set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;

-- 油品消费
insert overwrite table   bjshell.`res_oil` partition(dt)
select substr(X.transaction_date,1,16),X.station_id,X.product_code,count(X.product_code),sum(X.item_quantity),sum(X.item_value),dt
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.product_code,Y.item_quantity,Y.item_value,Z.dt
from
 ( select transaction_date,station_id,transaction_id,dt from bjshell.`transactions_parquet` where dt in ${dt} ) as Z
join transaction_items_oil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id )AS X
group by substr(X.transaction_date,1,16),X.station_id,X.product_code,dt;

-- 非油消费
insert overwrite table bjshell.res_notoil partition(dt)
select SUBSTR(transaction_date,1,16),station_id,count(DISTINCT transaction_id),sum(X.item_value),dt
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.item_value,Z.dt
from
 ( select transaction_date,station_id,transaction_id,dt from bjshell.`transactions_parquet` where dt in ${dt} ) as Z
join transaction_items_notoil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id)AS X
group by SUBSTR(transaction_date,1,16),station_id,dt;


insert overwrite table bjshell.res_notoilDepartment partition(dt)
select substr(X.transaction_date,1,16),X.station_id,X.dept_name,count(X.department_id),sum(X.item_value),dt
from
(select Z.transaction_date,Y.station_id,Y.transaction_id,Y.item_value,Y.department_id,C.dept_name,Z.dt
from
 ( select transaction_date,station_id,transaction_id,dt from bjshell.`transactions_parquet` where dt in ${dt} )   as Z
join transaction_items_notoil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id
join products as B
on Y.product_code = B.product_code and  B.station_id = 50006
join departments as C
on C.station_id = 50006 and B.department_id = C.department_id
)AS X
group by
substr(X.transaction_date,1,16),X.station_id,X.dept_name,dt;


insert overwrite table    bjshell.mid_notoilTop partition(dt)
select substr(X.transaction_date,1,10),X.station_id,X.item_description,X.product_code,sum(X.item_quantity),sum(X.item_value),dt
from
(select Y.transaction_id,Z.transaction_date,Y.station_id,Y.item_description,Y.item_value,Y.product_code,Y.item_quantity,Z.dt
from
  ( select transaction_date,station_id,transaction_id,dt from bjshell.`transactions_parquet` where dt in ${dt} )   as Z
join transaction_items_notoil as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id )AS X
group by substr(X.transaction_date,1,10),X.station_id,X.item_description,X.product_code,dt;


insert overwrite table  bjshell.res_notoilTop partition(dt)
select * from
(select minutes,stationID,top10,productCode,number,money,
row_number() over (partition by minutes,stationID order by number desc)AS rank,dt from bjshell.mid_notoilTop a where  dt in  ${dt}
)b
where b.rank<=10 ;

insert OVERWRITE table  bjshell.mid_lube partition(dt)
select DISTINCT d.transaction_date,d.station_id,d.transaction_id,d.item_value,d.oillitre,dt from
(
select A.transaction_id as transaction_id,C.transaction_date as transaction_date ,A.station_id as station_id,B.department_id as department_id,A.item_value as item_value ,trim(A.item_description)as oillitre,dt
from transaction_items_notoil as A
join products as B
on A.product_code = B.product_code and B.station_id = 50006
join ( select transaction_date,station_id,transaction_id,dt from bjshell.`transactions_parquet` where dt in ${dt} )  as C
on C.transaction_id = A.transaction_id
)d where d.department_id in (548,64);

insert overwrite table   bjshell.res_lube partition(dt)
select substr(minutes,1,16),stationID,count(DISTINCT transactionID),sum(substr(oillitre,-2,1)),sum(itemvalue)
from mid_lube where dt in ${dt}
group by substr(minutes,1,16),stationID;

insert overwrite table  bjshell.mid_notoilRate partition(dt)
select substr(transaction_date,1,13),station_id,count(*),dt
from bjshell.`transactions_parquet` where dt in ${dt}
group by substr(transaction_date,1,13),station_id;

insert overwrite table   bjshell.res_notoilRate partition(dt)
select A.houra,A.stationID,A.numbera,B.numberb,dt
FROM
(select SUBSTR(minutes,1,13)AS houra,stationID,sum(notoilNumber)AS numbera,dt
from bjshell.res_notoil where dt in ${dt}
group by SUBSTR(minutes,1,13),stationID) AS A
JOIN
(select substr(minutes,1,13)AS hourb,stationID,sum(tradeNumber)AS numberb
from bjshell.mid_notoilRate where dt in ${dt}
group by substr(minutes,1,13),stationID) AS B
ON A.houra=B.hourb and A.stationID=B.stationID;

INSERT OVERWRITE TABLE  res_oilPrice
select Y.effective_date,Y.station_id,Z.Grade_Name,Y.grade_price
from Grades as Z 
JOIN future_grade_price as Y 
on Z.Grade_ID = Y.grade_id and Z.station_id=Y.station_id
order by Y.effective_date;

insert overwrite table  res_oilPrice
select time,stationID,
case when oils = '-10# 柴油' then '-10#'
     when oils = '-20# 柴油' then '-20#'
     when oils = '-35#柴油' then '-35#'
     when oils = '0# 柴油' or oils = '0#柴油' then '0#'
     when oils = '92#京标(VI)车用汽油' or oils = '92#京标(VI)车用汽油.' or oils = '92#汽油' then '92#'
     when oils = '95#汽油' or oils = '95#京标(VI)车用汽油' then '95#'
else oils end,
oilprice from  res_oilPrice
where oils = '-10# 柴油'
or oils = '-20# 柴油' 
or oils = '-35#柴油' 
or oils = '0# 柴油' 
or oils = '0#柴油' 
or oils = '92#京标(VI)车用汽油' 
or oils = '92#京标(VI)车用汽油.' 
or oils = '92#汽油'
or oils = '95#京标(VI)车用汽油'
or oils = '95#汽油';

insert overwrite table   bjshell.res_mop partition(dt)
select substr(X.transaction_date,1,13),X.station_id,X.mop_name,count(X.item_quantity),sum(X.item_value),dt
from
(select Z.transaction_date,Y.station_id,Y.mop_name,Y.item_value,Y.item_quantity,dt
from
  ( select transaction_date,station_id,transaction_id,dt from bjshell.`transactions_parquet` where dt in ${dt} ) as Z
join transaction_items_mop as Y
on Z.transaction_id=Y.transaction_id and Z.station_id=Y.station_id and Y.item_value > 0)AS X
group by substr(X.transaction_date,1,13),X.station_id,X.mop_name,dt;

insert overwrite table  bjshell.res_HHTIPT partition(dt)
select B.substrb,B.station_id,A.count,A.sum,B.count-A.count,B.sum-A.sum,B.count,B.sum,dt
from
(select substr(transaction_date,1,13)as substrb,station_id,count(transaction_id)as count,sum(sale_total)as sum
from bjshell.`transactions_parquet` where dt in ${dt}
group by substr(transaction_date,1,13),station_id)as B
join
(select substr(Z.transaction_date,1,13)as substra,Z.station_id,count(Z.transaction_id)as count,sum(Z.sale_total)as sum,dt
from
 ( select transaction_date,station_id,transaction_id,sale_total,cashier_id,dt from bjshell.`transactions_parquet` where dt in ${dt} )  AS Z
JOIN cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name = 'HHT'
group by substr(Z.transaction_date,1,13),dt,Z.station_id) as A
on A.substra=B.substrb AND A.station_id=B.station_id;

insert overwrite table  bjshell.res_tr partition(dt)
select A.station_id,substr(A.transaction_date,1,7),sum(B.item_quantity),dt
from  ( select transaction_date,station_id,transaction_id,dt from bjshell.`transactions_parquet` where dt in ${dt} )  as A
join transaction_items_oil as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id
group by A.station_id,substr(A.transaction_date,1,7),dt;

set hive.auto.convert.join=false;

insert overwrite table bjshell.res_mopipt  partition(dt)
select substr(B.transaction_date,1,13),A.station_id,A.mop_name,count(A.item_quantity),sum(A.item_value),dt
from transaction_items_mop as A
join
(select Z.station_id,Z.transaction_id,Z.transaction_date,dt
from
( select transaction_date,station_id,transaction_id,cashier_id,dt from bjshell.`transactions_parquet` where dt in ${dt} ) AS Z
JOIN cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name != 'HHT') as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id and A.item_value > 0
group by substr(B.transaction_date,1,13),A.station_id,A.mop_name,dt;


set hive.auto.convert.join=false;

insert overwrite table  bjshell.res_mophht  partition(dt)
select substr(B.transaction_date,1,13),A.station_id,A.mop_name,count(A.item_quantity),sum(A.item_value),dt
from transaction_items_mop as A
join
(select Z.station_id,Z.transaction_id,Z.transaction_date,dt
from
( select transaction_date,station_id,transaction_id,cashier_id,dt from bjshell.`transactions_parquet` where dt in ${dt} )  AS Z
JOIN cashiers AS Y
on Z.cashier_id=Y.cashier_id and Z.station_id=Y.station_id and Y.cashier_name = 'HHT') as B
on A.transaction_id=B.transaction_id and A.station_id=B.station_id and A.item_value > 0
group by substr(B.transaction_date,1,13),A.station_id,A.mop_name,dt;

insert OVERWRITE table   bjshell.mid_exceptLube  partition(dt)
select DISTINCT d.transaction_date,d.station_id,d.transaction_id,d.item_value,dt from
(
select A.transaction_id as transaction_id,C.transaction_date as transaction_date ,A.station_id as station_id,B.department_id as department_id,A.item_value as item_value ,dt
from transaction_items_notoil as A
join products as B
on A.product_code = B.product_code and B.station_id = 50006
join ( select transaction_date,station_id,transaction_id,dt from bjshell.`transactions_parquet` where dt in ${dt} )  as C
on C.transaction_id = A.transaction_id and C.station_id = A.station_id )d
where d.department_id not in (548,64);

insert overwrite table   bjshell.res_exceptLube  partition(dt)
select substr(minutes,1,16),stationID,count(DISTINCT transactionID),sum(itemvalue),dt
from  bjshell.mid_exceptLube  where dt in ${dt}
group by substr(minutes,1,16),stationID;

-- insert overwrite table   bjshell.res_exceptLube  partition(dt='all')
-- select `minutes`, `stationid` ,  `dealnum` ,  `itemvalue` from  bjshell.res_exceptLube where dt<'all';


